package com.navinfo.tripanalysis.util;

import com.github.yt.excel.annotations.ExportExcel;
import com.github.yt.excel.annotations.ImportExcel;
import com.github.yt.excel.enums.ExcelExceptionEnum;
import com.github.yt.excel.exception.ParseExcelException;
import com.github.yt.excel.vo.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.script.ScriptEngine;
import javax.script.ScriptEngineManager;
import javax.script.ScriptException;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;


public class ExcelUtils {
    /**
     * 根据file文件、class类配置转换成list集合，默认从第二行开始读.
     *
     * @param file  file
     * @param clazz class类
     * @return 对象集合
     * @throws ParseExcelException
     */
    public static <T> List<T> readExcel(File file, Class<T> clazz) throws ParseExcelException {
        return readExcel(file, clazz, new ExcelConfig().setStartRow(2));
    }

    /**
     * 根据file文件、class类配置转换成list集合.
     *
     * @param file        file
     * @param clazz       class类
     * @param excelConfig 各种配置参数
     * @return 对象集合
     * @throws ParseExcelException
     */
    public static <T> List<T> readExcel(File file, Class<T> clazz, ExcelConfig excelConfig) throws ParseExcelException {
        try {
            return readExcel(new FileInputStream(file), file.getName(), clazz, excelConfig);
        } catch (IOException e) {
            throw new ParseExcelException(e);
        }
    }



    /**
     * 根据excel文件、class类配置转换成list集合.
     *
     * @param inputStream excel文件输入流
     * @param clazz       class类
     * @param excelConfig 各种配置参数
     * @return 对象集合
     * @throws ParseExcelException
     */
    private static <T> List<T> readExcel(InputStream inputStream, String fileName, Class<T> clazz, ExcelConfig excelConfig) throws ParseExcelException {
        //validate
        if (inputStream == null) {
            throw new IllegalArgumentException("inputStream can not be null");
        }
        int startRow = excelConfig.getStartRow();
        if (startRow < 1) {
            startRow = 1;
        }
        //初始化结果集
        List list = new ArrayList<>();
        //poi对象
        Workbook workbook;
        String errorRow = "";
        String errorCell = "";
        try {
            //初始化excel列与对象的映射关系
            Map<Integer, ImportExcelVo> orderMap = initAnnotationsMap(clazz);
            workbook = initWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            int rowNum = sheet.getLastRowNum();
            if (rowNum == 0) {
                return list;
            }
            int colNum = sheet.getRow(0).getLastCellNum();
            //循环行
            for (int i = startRow - 1; i <= rowNum; i++) {
                Row row = sheet.getRow(i);
                errorRow = (row.getRowNum() + 1) + "";
                if (row == null) {
                    continue;
                }
                Object o = clazz.newInstance();
                // 判断整行是否都为空
                boolean allEmpty = true;
                for (int j = 0; j < colNum; j++) {
                    if (orderMap.get(j + 1) == null) {
                        continue;
                    }
                    Cell cell = row.getCell(j);
                    if (cell != null) {
                        ImportExcelVo importExcelVo = orderMap.get(j + 1);
                        //获取cell对应的值
                        String val = getCellValue(cell, importExcelVo);
                        if (org.apache.commons.lang3.StringUtils.isNotEmpty(val)) {
                            allEmpty = false;
                        }
                    }
                }
                if (allEmpty) {
                    continue;
                }
                //循环列
                for (int j = 0; j < colNum; j++) {
                    if (orderMap.get(j + 1) == null) {
                        continue;
                    }
                    Cell cell = row.getCell(j);
                    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                        if (cell instanceof XSSFCell) {
                            errorCell = ((XSSFCell) cell).getReference();
                        }
                        ImportExcelVo importExcelVo = orderMap.get(j + 1);
                        //获取cell对应的值
                        String val = getCellValue(cell, importExcelVo);
                        //赋到对象中
                        setObjValue(o, importExcelVo, val);
                        errorCell = "";
                    }
                }
                list.add(o);
            }
        } catch (NumberFormatException e) {
            if (org.apache.commons.lang3.StringUtils.isNotBlank(errorCell)) {
                String[] str_string = errorCell.split("\\d");//  \d 为正则表达式表示[0-9]数字
                String lie = str_string[0];
                String hang = errorCell.substring(str_string[0].length());
                throw new ParseExcelException(ExcelExceptionEnum.ERROR_401, ("(第[" + hang + "]行，第[" + lie + "]列)"));
            }
            throw new ParseExcelException(ExcelExceptionEnum.ERROR_401);
        } catch (Exception e) {
            throw new ParseExcelException(ExcelExceptionEnum.ERROR_402);
        } finally {
            try {
                inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    private static Workbook initWorkbook(InputStream inputStream) throws IOException, ParseExcelException, InvalidFormatException {
//        return WorkbookFactory.create(inputStream);
        return new XSSFWorkbook(inputStream);
//        if (fileName.endsWith("xls")) {
//            return new HSSFWorkbook(inputStream);
//        } else if (fileName.endsWith("xlsx")) {
//            return new XSSFWorkbook(inputStream);
//        } else {
//            throw new ParseExcelException("can not read excel file!");
//        }
    }

    private static Map<Integer, ImportExcelVo> initAnnotationsMap(Class<?> clazz) {
        Field[] fields = clazz.getDeclaredFields();
        Map<Integer, ImportExcelVo> map = new HashMap<>();
        for (Field field : fields) {
            String fieldName = field.getName();
            if (fieldName != null && field.isAnnotationPresent(ImportExcel.class)) {
                ImportExcel sign = field.getAnnotation(ImportExcel.class);
                ImportExcelVo importExcelVo = new ImportExcelVo();
                importExcelVo.setField(field);
                importExcelVo.setDateFormat(sign.dateFormat());
                importExcelVo.setJavaScriptBody(sign.javaScriptBody());
                importExcelVo.setOrder(sign.order());
                map.put(sign.order(), importExcelVo);
            }
        }
        return map;
    }

    private static void setObjValue(Object source, ImportExcelVo importExcelVo, String value) throws IllegalAccessException, ParseException, ScriptException {
        Field field = importExcelVo.getField();
        Class clazz = field.getType();
        Object val = null;
        boolean hasJavaScriptBody = importExcelVo.getJavaScriptBody() != null && !"".equals(importExcelVo.getJavaScriptBody());
        if (hasJavaScriptBody) {
            val = exeScript(value, importExcelVo);
        } else {
            if(value!=null&&!"".equalsIgnoreCase(value.trim())) {
                if (clazz.equals(byte.class) || clazz.equals(Byte.class)) {
                    val = Byte.valueOf(value);
                } else if (clazz.equals(short.class) || clazz.equals(Short.class)) {
                    val = Short.valueOf(value);
                } else if (clazz.equals(int.class) || clazz.equals(Integer.class)) {
                    val = Integer.valueOf(value);
                } else if (clazz.equals(long.class) || clazz.equals(Long.class)) {
                    val = Long.valueOf(value);
                } else if (clazz.equals(float.class) || clazz.equals(Float.class)) {
                    val = Float.valueOf(value);
                } else if (clazz.equals(double.class) || clazz.equals(Double.class)) {
                    val = Double.valueOf(value);
                } else if (clazz.equals(BigDecimal.class)) {
                    val = new BigDecimal(value);
                } else if (clazz.equals(Date.class)) {
                    SimpleDateFormat sdf = new SimpleDateFormat(importExcelVo.getDateFormat());
                    val = sdf.parse(value);
                } else if (clazz.equals(Boolean.class) || clazz.equals(boolean.class)) {
                    val = Boolean.valueOf(value);
                } else if (String.class.equals(clazz)) {
                    val = value;
                }
            }
        }
        if (val != null) {
            field.setAccessible(true);
            field.set(source, val);
        }
    }

    private static Object exeScript(Object val, ImportExcelVo importExcelVo) throws ScriptException {
        ScriptEngineManager engineManager = new ScriptEngineManager();
        ScriptEngine engine = engineManager.getEngineByName("JavaScript");
        engine.eval("function run(param){ " + importExcelVo.getJavaScriptBody() + "}");
        if (String.class.equals(val.getClass())) {
            val = "'" + val + "'";
        }
        return engine.eval("run(" + val + ");");
    }

    private static String getCellValue(final Cell cell, ImportExcelVo importExcelVo) {
        String value = null;
        if (cell == null) {
            return value;
        }
        try {
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    value = String.valueOf(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        SimpleDateFormat sdf = new SimpleDateFormat(importExcelVo.getDateFormat());
                        value = sdf.format(cell.getDateCellValue());
                    } else {
                        DecimalFormat df = new DecimalFormat("#.##");
                        value = df.format(cell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_BLANK:
                case Cell.CELL_TYPE_ERROR:
                    value = "";
                    break;
                default:
                    value = "";
                    break;
            }
        }catch (Exception ex){
            ex.printStackTrace();
        }
        return value;
    }

}
